using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using CoreLab.Oracle;

namespace Common.Utilities
{
    /// <summary>
    /// 
    /// </summary>
    public class SchemaUtil
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="schemaObj"></param>
        /// <returns></returns>
        public static string GetConnectionString(SchemaObject schemaObj)
        {
            string connStr = "Data Source=" + schemaObj.DataSource;
            connStr += ";";
            if (schemaObj.Provider == DataProviderType.MSSQL && (!string.IsNullOrEmpty(schemaObj.CatalogName)))
            {
                connStr += "database=" + schemaObj.CatalogName + ";";
            }
            if (schemaObj.AuthType == AuthenticationType.WindowsAuthentication)
            {
                connStr += "integrated security=true;";
            }
            else
            {
                connStr += "user id=" + schemaObj.UserID + ";password=" + schemaObj.Password + ";";
            }
            return connStr;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="schemaObj"></param>
        /// <returns></returns>
        public static TableColumn[] GetTableColumns(SchemaObject schemaObj)
        {
            if (!string.IsNullOrEmpty(schemaObj.SelectCommand))
            {
                TableColumn[] cols = null;
                string connStr = SchemaUtil.GetConnectionString(schemaObj);
                switch (schemaObj.Provider)
                {
                    case DataProviderType.MSSQL:
                        if (!string.IsNullOrEmpty(schemaObj.SelectCommand))
                        {
                            cols = SchemaUtil.GetTableColsFromMssql(connStr, schemaObj.SelectCommand);
                        }
                        else
                        {
                            cols = DbUtil.GetTableColumns(schemaObj.Provider, connStr, schemaObj.Schema, schemaObj.TableName);
                        }
                        break;
                    case DataProviderType.Oracle:
                        if (!string.IsNullOrEmpty(schemaObj.SelectCommand))
                        {
                            cols = SchemaUtil.GetTableColsFromOracle(connStr, schemaObj.SelectCommand);
                        }
                        else
                        {
                            cols = DbUtil.GetTableColumns(schemaObj.Provider, connStr, schemaObj.Schema, schemaObj.TableName);
                        }
                        break;
                }
                return cols;
            }
            else
            {
                return DbUtil.GetTableColumns(
                    schemaObj.Provider,
                    SchemaUtil.GetConnectionString(schemaObj),
                    schemaObj.Schema,
                    schemaObj.TableName);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="selectSql"></param>
        /// <returns></returns>
        public static TableColumn[] GetTableColsFromMssql(string connStr, string selectSql)
        {
            if (!selectSql.ToLower().StartsWith("select "))
            {
                throw new Exception("Invalid select command: sql must starts with 'select' key word");
            }
            SqlConnection conn = new SqlConnection(connStr);
            List<TableColumn> cols = new List<TableColumn>();
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                
                if (selectSql.ToLower().IndexOf("select top") < 0)
                {
                    selectSql ="select top 1 " + selectSql.Substring("select ".Length);
                }
                cmd.CommandText = selectSql;
                SqlDataReader reader = cmd.ExecuteReader();
                DataTable dtSchema = reader.GetSchemaTable();
                for (int i = 0; i < dtSchema.Rows.Count; i++)
                {
                    TableColumn field = new TableColumn();
                    field.AllowNull = (bool)dtSchema.Rows[i]["AllowDBNull"];
                    field.ColumnName = dtSchema.Rows[i]["ColumnName"].ToString();
                    string dataType = dtSchema.Rows[i]["DataType"].ToString();
                    field.DbType = FieldDataType.ToDbType(dataType);
                    field.IsIdentity = (bool)dtSchema.Rows[i]["IsIdentity"];
                    try
                    {
                        bool isKey = (bool)dtSchema.Rows[i]["IsKey"];
                        bool isUnique = (bool)dtSchema.Rows[i]["IsUnique"];
                        if (isKey && isUnique)
                        {
                            field.IsPK = true;
                        }
                        else
                        {
                            field.IsPK = false;
                        }
                    }
                    catch
                    {
                        field.IsPK = false;
                    }
                    field.Ordinal = int.Parse(dtSchema.Rows[i]["ColumnOrdinal"].ToString());
                    field.Precision = 0;
                    try
                    {
                        field.Precision = int.Parse(dtSchema.Rows[i]["NumericPrecision"].ToString());
                    }
                    catch
                    {
                        field.Precision = 0;
                    }
                    field.Scale = 0;
                    try
                    {
                        field.Scale = int.Parse(dtSchema.Rows[i]["NumericScale"].ToString());
                    }
                    catch
                    {
                        field.Scale = 0;
                    }
                    field.Size = 0;
                    try
                    {
                        field.Size = int.Parse(dtSchema.Rows[i]["ColumnSize"].ToString());
                    }
                    catch
                    {
                        field.Size = 0;
                    }
                    cols.Add(field);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return cols.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="selectSql"></param>
        /// <returns></returns>
        public static TableColumn[] GetTableColsFromOracle(string connStr, string selectSql)
        {
            if (!selectSql.ToLower().StartsWith("select "))
            {
                throw new Exception("Invalid select command: sql must starts with 'select' key word");
            }
            OracleConnection conn = new OracleConnection(connStr);
            List<TableColumn> cols = new List<TableColumn>();
            try
            {
                conn.Open();
                OracleCommand cmd = conn.CreateCommand();
                if (selectSql.IndexOf(";") > 0)
                {
                    throw new Exception("Oracle driver does not support batch execution of sql command, try removing \";\"");
                }
                cmd.CommandText = selectSql;
                OracleDataReader reader = cmd.ExecuteReader();
                DataTable dtSchema = reader.GetSchemaTable();
                for (int i = 0; i < dtSchema.Rows.Count; i++)
                {
                    TableColumn field = new TableColumn();
                    field.AllowNull = (bool)dtSchema.Rows[i]["AllowDBNull"];
                    field.ColumnName = dtSchema.Rows[i]["ColumnName"].ToString();
                    string dataType = dtSchema.Rows[i]["DataType"].ToString();
                    field.DbType = FieldDataType.ToDbType(dataType);
                    field.IsIdentity = false;
                    try
                    {
                        bool isKey = (bool)dtSchema.Rows[i]["IsKey"];
                        bool isUnique = (bool)dtSchema.Rows[i]["IsUnique"];
                        if (isKey && isUnique)
                        {
                            field.IsPK = true;
                        }
                        else
                        {
                            field.IsPK = false;
                        }
                    }
                    catch
                    {
                        field.IsPK = false;
                    }
                    field.Ordinal = int.Parse(dtSchema.Rows[i]["ColumnOrdinal"].ToString());
                    field.Precision = 0;
                    try
                    {
                        field.Precision = int.Parse(dtSchema.Rows[i]["NumericPrecision"].ToString());
                    }
                    catch
                    {
                        field.Precision = 0;
                    }
                    field.Scale = 0;
                    try
                    {
                        field.Scale = int.Parse(dtSchema.Rows[i]["NumericScale"].ToString());
                    }
                    catch
                    {
                        field.Scale = 0;
                    }
                    field.Size = 0;
                    try
                    {
                        field.Size = int.Parse(dtSchema.Rows[i]["ColumnSize"].ToString());
                    }
                    catch
                    {
                        field.Size = 0;
                    }
                    cols.Add(field);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return cols.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static SchemaObject[] GetSchemaObjects(DataProviderType provider, string connStr)
        {
            List<SchemaObject> schemaObjs = new List<SchemaObject>();
            AuthenticationType authType = DbConnUtil.GetAuthenticationType(connStr);
            string dataSrc = DbConnUtil.GetHost(connStr);
            string uid = DbConnUtil.GetUserID(connStr);
            string pwd = DbConnUtil.GetPassword(connStr);
            if (provider == DataProviderType.Oracle)
            {
                OracleConnection oraConn = new OracleConnection(connStr);
                try
                {
                    oraConn.Open();
                    System.Data.DataTable dt_TableList = oraConn.GetSchema("Tables");
                    if (dt_TableList.Rows.Count > 0)
                    {
                        foreach (System.Data.DataRow drTbl in dt_TableList.Rows)
                        {
                            string schemaName = drTbl[0].ToString();
                            string tblName = drTbl[1].ToString();
                            SchemaObject schemaObj = new SchemaObject();
                            schemaObj.AuthType = authType;
                            schemaObj.CatalogName = string.Empty;
                            schemaObj.DataSource = dataSrc;
                            schemaObj.Password = pwd;
                            schemaObj.Provider = DataProviderType.Oracle;
                            schemaObj.Schema = schemaName;
                            schemaObj.TableName = tblName;
                            schemaObj.UserID = uid;
                            schemaObjs.Add(schemaObj);
                        }
                    }
                    System.Data.DataTable dtViewList = oraConn.GetSchema("Views");
                    if (dtViewList.Rows.Count > 0)
                    {
                        foreach (System.Data.DataRow drView in dtViewList.Rows)
                        {
                            string schemaName = drView[0].ToString();
                            string viewName = drView[1].ToString();
                            SchemaObject schemaObj = new SchemaObject();
                            schemaObj.AuthType = authType;
                            schemaObj.CatalogName = string.Empty;
                            schemaObj.DataSource = dataSrc;
                            schemaObj.Password = pwd;
                            schemaObj.Provider = DataProviderType.Oracle;
                            schemaObj.Schema = schemaName;
                            schemaObj.TableName = viewName;
                            schemaObj.UserID = uid;
                            schemaObjs.Add(schemaObj);
                        }
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    oraConn.Close();
                }
            }
            else
            {
                SqlConnection sqlConn = new SqlConnection(connStr);
                try
                {
                    sqlConn.Open();
                    System.Data.DataTable dt_TableList = sqlConn.GetSchema("Tables");
                    if (dt_TableList.Rows.Count > 0)
                    {
                        foreach (System.Data.DataRow drTbl in dt_TableList.Rows)
                        {
                            string schemaName = drTbl[0].ToString();
                            string tblName = drTbl[1].ToString();
                            SchemaObject schemaObj = new SchemaObject();
                            schemaObj.AuthType = authType;
                            schemaObj.CatalogName = string.Empty;
                            schemaObj.DataSource = dataSrc;
                            schemaObj.Password = pwd;
                            schemaObj.Provider = DataProviderType.Oracle;
                            schemaObj.Schema = schemaName;
                            schemaObj.TableName = tblName;
                            schemaObj.UserID = uid;
                            schemaObjs.Add(schemaObj);
                        }
                    }
                    System.Data.DataTable dtViewList = sqlConn.GetSchema("Views");
                    if (dtViewList.Rows.Count > 0)
                    {
                        foreach (System.Data.DataRow drView in dtViewList.Rows)
                        {
                            string schemaName = drView[0].ToString();
                            string viewName = drView[1].ToString();
                            SchemaObject schemaObj = new SchemaObject();
                            schemaObj.AuthType = authType;
                            schemaObj.CatalogName = string.Empty;
                            schemaObj.DataSource = dataSrc;
                            schemaObj.Password = pwd;
                            schemaObj.Provider = DataProviderType.Oracle;
                            schemaObj.Schema = schemaName;
                            schemaObj.TableName = viewName;
                            schemaObj.UserID = uid;
                            schemaObjs.Add(schemaObj);
                        }
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    sqlConn.Close();
                }
            }

            if (schemaObjs.Count > 0)
            {
                return schemaObjs.ToArray();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static SchemaObject[] GetTables(DataProviderType provider, string connStr)
        {
            List<SchemaObject> schemaObjs = new List<SchemaObject>();
            AuthenticationType authType = DbConnUtil.GetAuthenticationType(connStr);
            string dataSrc = DbConnUtil.GetHost(connStr);
            string uid = DbConnUtil.GetUserID(connStr);
            string pwd = DbConnUtil.GetPassword(connStr);
            if (provider == DataProviderType.Oracle)
            {
                OracleConnection oraConn = new OracleConnection(connStr);
                try
                {
                    oraConn.Open();
                    System.Data.DataTable dt_TableList = oraConn.GetSchema("Tables");
                    if (dt_TableList.Rows.Count > 0)
                    {
                        foreach (System.Data.DataRow drTbl in dt_TableList.Rows)
                        {
                            string schemaName = drTbl[0].ToString();
                            string tblName = drTbl[1].ToString();
                            SchemaObject schemaObj = new SchemaObject();
                            schemaObj.AuthType = authType;
                            schemaObj.CatalogName = string.Empty;
                            schemaObj.DataSource = dataSrc;
                            schemaObj.Password = pwd;
                            schemaObj.Provider = DataProviderType.Oracle;
                            schemaObj.Schema = schemaName;
                            schemaObj.TableName = tblName;
                            schemaObj.UserID = uid;
                            schemaObjs.Add(schemaObj);
                        }
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    oraConn.Close();
                }
            }
            else
            {
                SqlConnection sqlConn = new SqlConnection(connStr);
                try
                {
                    sqlConn.Open();
                    System.Data.DataTable dt_TableList = sqlConn.GetSchema("Tables");
                    if (dt_TableList.Rows.Count > 0)
                    {
                        foreach (System.Data.DataRow drTbl in dt_TableList.Rows)
                        {
                            string schemaName = drTbl[0].ToString();
                            string tblName = drTbl[1].ToString();
                            SchemaObject schemaObj = new SchemaObject();
                            schemaObj.AuthType = authType;
                            schemaObj.CatalogName = string.Empty;
                            schemaObj.DataSource = dataSrc;
                            schemaObj.Password = pwd;
                            schemaObj.Provider = DataProviderType.Oracle;
                            schemaObj.Schema = schemaName;
                            schemaObj.TableName = tblName;
                            schemaObj.UserID = uid;
                            schemaObjs.Add(schemaObj);
                        }
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    sqlConn.Close();
                }
            }

            if (schemaObjs.Count > 0)
            {
                return schemaObjs.ToArray();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static SchemaObject[] GetViews(DataProviderType provider, string connStr)
        {
            List<SchemaObject> schemaObjs = new List<SchemaObject>();
            AuthenticationType authType = DbConnUtil.GetAuthenticationType(connStr);
            string dataSrc = DbConnUtil.GetHost(connStr);
            string uid = DbConnUtil.GetUserID(connStr);
            string pwd = DbConnUtil.GetPassword(connStr);
            if (provider == DataProviderType.Oracle)
            {
                OracleConnection oraConn = new OracleConnection(connStr);
                try
                {
                    oraConn.Open();
                    System.Data.DataTable dtViewList = oraConn.GetSchema("Views");
                    if (dtViewList.Rows.Count > 0)
                    {
                        foreach (System.Data.DataRow drView in dtViewList.Rows)
                        {
                            string schemaName = drView[0].ToString();
                            string viewName = drView[1].ToString();
                            SchemaObject schemaObj = new SchemaObject();
                            schemaObj.AuthType = authType;
                            schemaObj.CatalogName = string.Empty;
                            schemaObj.DataSource = dataSrc;
                            schemaObj.Password = pwd;
                            schemaObj.Provider = DataProviderType.Oracle;
                            schemaObj.Schema = schemaName;
                            schemaObj.TableName = viewName;
                            schemaObj.UserID = uid;
                            schemaObjs.Add(schemaObj);
                        }
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    oraConn.Close();
                }
            }
            else
            {
                SqlConnection sqlConn = new SqlConnection(connStr);
                try
                {
                    sqlConn.Open();
                    System.Data.DataTable dtViewList = sqlConn.GetSchema("Views");
                    if (dtViewList.Rows.Count > 0)
                    {
                        foreach (System.Data.DataRow drView in dtViewList.Rows)
                        {
                            string schemaName = drView[0].ToString();
                            string viewName = drView[1].ToString();
                            SchemaObject schemaObj = new SchemaObject();
                            schemaObj.AuthType = authType;
                            schemaObj.CatalogName = string.Empty;
                            schemaObj.DataSource = dataSrc;
                            schemaObj.Password = pwd;
                            schemaObj.Provider = DataProviderType.Oracle;
                            schemaObj.Schema = schemaName;
                            schemaObj.TableName = viewName;
                            schemaObj.UserID = uid;
                            schemaObjs.Add(schemaObj);
                        }
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    sqlConn.Close();
                }
            }

            if (schemaObjs.Count > 0)
            {
                return schemaObjs.ToArray();
            }
            else
            {
                return null;
            }
        }
    }
}
